# @Project: diablo
# @File: agreement_tag_discount_prod
# @Time: 2024/5/8 17:44
# @User: Jolly
# @Email: jolly@007vin.com

"""
所有商户，销售价，原厂品质定价，计算折扣价，打标签(正式环境)
"""

import datetime
from datetime import timedelta
import time
import threading
import requests
from queue import Queue
from copy import deepcopy
import uuid

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient, InsertOne, UpdateOne
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class InitAgreementPriceTag:

    def __init__(self):

        self.hongde_main = ["3"]  # 总仓

        self.hongde_yixian = ["1390f84351fe464b90dcdbf3a164ff24"]  # 一线

        self.main_group = ["3", "1390f84351fe464b90dcdbf3a164ff24"]  # 总仓和一线

        # 宏德分公司（除了总仓和一线）
        self.hongde_group = ["fc2e72e26a03423c8f8ee8576debf2ec",  # 龙华鑫众奥
                             "3fd50208a10c42ac87c6d84bd9f5661f",  # 龙华名捷
                             "a62152e4fe1f49d186d23e060f272e65",  # 龙华名诚行
                             "1b5044001e05415ab9f19afbeb73b9c7",  # 罗湖德众
                             "3d80a9253b8640d4afc1da174a39d085",  # 罗湖名捷
                             "230afea849fd489d88aa6dfeab26a3b3",  # 罗湖名诚行
                             "8f5992a1d102410e95cd76f70f0cec30",  # 龙岗德众
                             "c217b4952279478889397f15d9968735",  # 龙岗名捷
                             "84c2ec02335b4f7f8cabcbbda0d133af",  # 龙岗名诚行
                             "411e70375f484ae0b7948b326401a205",  # 平湖鑫德众
                             "b8b351a60d114d3a8a078ad178eb41c5",  # 平湖名捷
                             "f73a74ddc6584457bd573b400e217110",  # 平湖名诚行
                             "0e402537190b4677b5d6d8a1c8af0b2f",  # 寮步鑫众奥
                             "c7823ee69b7b4ed4ae8ddab3ee0c12c7",  # 寮步路捷
                             "92560c123ce347b9806b6756a0d54097",  # 寮步名诚行
                             "79f7412227fb4928bb12f2dc11048a11",  # 沙井鑫众奥
                             "4b2c95dd10654036909423de19777d5d",  # 沙井名捷
                             "920980642c63490abfad211727bbee7d",  # 沙井名诚行
                             "e44f3831dede4640b9bdd40eeaabdc26",  # 宝安鑫众奥
                             "50ff6b30688d40b48879857847dcc320",  # 宝安名捷
                             "192b5c4f311442cbb613ed63a07d8f78",  # 宝安名诚行
                             "c982247f26b74166884d0ca2087d5c17",  # 厚街鑫众奥
                             "acf15daf375f497390ac274538e3e058",  # 厚街名捷
                             "a2f1c6009ca44cbf8b8e6ddb685b5eae",  # 厚街名诚行
                             "3546da2f191b4d1cb8979dad8e42593f",  # 塘厦鑫众奥
                             "203ffd0f7d3645f4ba59387b6b532308",  # 塘厦名捷
                             "cd5ce52f2db34e39bc66e88bc41238b0"   # 塘厦名诚行
                             ]

        self.main_group_dev = ["e36cf07027ec40aca7be752d45dc84d6", "19752aadc37746fda4023a2bfffa46c1"]  # 总仓和一线

        # 宏德分公司（除了总仓和一线）
        self.hongde_group_dev = ["19752aadc37746fda4023a2bfffa46c1",
                             "9fd96887306d4594ac85c9bb3404ae98",
                             "d7dc91c7d52d4cf79a2795385850babc",
                             "584a3ff537854102aff842c3e34daced",
                             "7813ff2f20d04bd58252b6c18735c392",
                             "e086c6ac452645c2822ad836ac28847f",
                             "da88bd366a6049f6b70c5f6395912396",
                             "0f60506053af4907b901b6d9ca132bb1",
                             "59e326e756a641c18c0d58d0df82030e",
                             "6d8dc5cc39ea431b81c88e53c213098b",
                             "23b63424bf3d4a87a51bac7049e7570c",
                             "9ba515ff52b84ef59370999f133246dc",
                             "9b726e87f1ea4255a1378d556cc9f5e2",
                             "f8d1ed3723214e9cb083f89cc4348903",
                             "3783bda80d78415cbed62fb88e1cab0d",
                             "5b612024616e47858bee94bc019aed7c",
                             "5303f5a79de84e7393e81c8778d69e0e",
                             "72a079ec9c4a415aab520d48d52bc1d0",
                             "b26d4b19a7004b0fac163409de616d93",
                             "bd86b3f4e2d640a8ba54d81adf50a502",
                             "6c124d09c73f4a0e91fb2ec947290cd8",
                             "83194859bf1948f3af2c4725bf311ed0",
                             "18967e237c6b400da8b8e2ccd4a2379d",
                             "c6184953d5d8406fa58d7c3c713a4ad2",
                             "ed2fbe6b0700468bb6c6f93cdf7fc5a8",
                             "91c80181631046be9bfcaec866a4e87a",
                             "b938852583494fada612a7bcb34e43a2",
                             "296e636203a841aa9246fbd630ab259f"]

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=300,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 200  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    # 判断定价协议表是否已有对应数据，有的话就删除
    def check_agreement_exist(self, code, agreement_excel_id, merchant_id):

        check = False
        agreement_price = 0

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    "agreement_excel_id": agreement_excel_id
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    # 获取定价
                    docs = conn.find(filter_dict, {'agreement_price': 1})

                    for doc in docs:
                        agreement_price = doc.get("agreement_price", 0)
                        if agreement_price != 0:
                            check = True
                            break

                    return check, agreement_price

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check, agreement_price

    def get_merchant_agreement_excel(self, merchant_id):
        """
        获取商家价格模板列表
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"select id from diablo_excel_agreement where merchant_id='{merchant_id}' and" \
                  f" status=1 and default_status=1"

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:
                return False, result[0][0]
            else:
                return True, 0

        except Exception as e:
            print("获取价格模板信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

    def get_4s_price(self, oe):
        """
        获取4s价格
        """

        oe = oe.replace(" ", "")

        params = {
            "pid": oe
        }
        url = f'http://192.168.191.245:9500/epc/engine/parts/search'

        headers = {
            "Content-Type": "application/json"
        }

        resp = requests.get(url, json=params, timeout=5, headers=headers).json()

        # response = requests.get(url=url, headers=headers, params=data)
        # resp = response.json()

        # print(resp)

        if resp.get('code') != 1:
            print("获取4s价失败")

        data = resp.get('data', [])
        price_4s = 0
        for item in data:
            break_flag = False
            for brand_info in item['brand_list']:
                if brand_info.get('price', 0):
                    price_4s = brand_info.get('price', 0)
                    break_flag = True
                    break
            if break_flag:
                break

        return price_4s

    def get_goods_info(self, merchant_id, code):
        """
        获取该商品goods信息
        """
        check = False
        tag_ids = []

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["goods"]

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    'is_delete_time': {"$exists": False}
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    # 获取标签id
                    docs = conn.find(filter_dict, {'tag_ids': 1})

                    for doc in docs:
                        tag_ids = doc.get("tag_ids", [])

                        if len(tag_ids) > 0:
                            check = True
                            break

                    return check, tag_ids

        except Exception as e:
            print("获取该商品goods信息失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check, tag_ids

    def write_tag_goods(self, merchant_id, code, tags_ids):
        """
        更新写入标签信息到goods表里
        """

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["goods"]

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    'is_delete_time': {"$exists": False}
                }

                update_dict = {
                    "$set": {
                        "tag_ids": tags_ids
                    }
                }

                conn.find_one_and_update(filter=filter_dict, update=update_dict, projection={'tag_ids': 1})

        except Exception as e:
            print("更新写入标签信息到goods表里失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return

    # 写入数据
    def write_data(self, *args):

        goods = args[0]
        stock_related = args[1]
        agreement_excel_id = args[2]
        quality_dict = args[3]
        discount_tag_data = args[4]

        code = goods.get("code")

        oe = goods.get("standard_code")

        # 获取定价记录，获取定价
        check, agreement_price = self.check_agreement_exist(code, agreement_excel_id,
                                                            stock_related.get("merchant_id"))
        if not check:
            return

        # erp品质
        brand_names = goods.get("brand_names")
        brand_names = str(brand_names).strip()

        # 获取4s价格
        price_4s = self.get_4s_price(oe)
        if float(price_4s) == 0:
            return

        # 获取品质，如果不是原厂，则跳过
        quality_name = quality_dict.get(brand_names, brand_names)

        if quality_name != "原厂":
            return

        # 定价/4s价小于1的商品可以打上折扣标签
        discount_value = float(agreement_price) / float(price_4s)
        if discount_value > 1:
            return

        discount_str = ""
        # 计算打折幅度
        if 0 < discount_value < 0.1:
            discount_str = "一折"
        elif 0.1 < discount_value <= 0.2:
            discount_str = "二折"
        elif 0.2 < discount_value <= 0.3:
            discount_str = "三折"
        elif 0.3 < discount_value <= 0.4:
            discount_str = "四折"
        elif 0.4 < discount_value <= 0.5:
            discount_str = "五折"
        elif 0.5 < discount_value <= 0.6:
            discount_str = "六折"
        elif 0.6 < discount_value <= 0.7:
            discount_str = "七折"
        elif 0.7 < discount_value <= 0.8:
            discount_str = "八折"
        elif 0.8 < discount_value <= 0.9:
            discount_str = "九折"

        # print(discount_str)

        # 获取当前公司对应标签id
        discount_tag_id = discount_tag_data.get(discount_str)
        if not discount_tag_id:
            return

        # print(discount_tag_id)

        # 获取折扣标签id列表
        discount_tag_ids = list(discount_tag_data.values())

        # print(discount_tag_ids)

        tags_ids = []
        # 查询当前商品，goods表
        check, tag_ids = self.get_goods_info(stock_related.get("merchant_id"), code)
        if not check or len(tag_ids) == 0:
            # 如果没有查找到标签，则写入该折扣id进goods表
            tags_ids = [discount_tag_id]
        else:
            # 比对tags记录，有折扣记录，且有变更的话就更换

            # 如果要打的标签已经在里面了，就直接返回，不用更新或者写入
            if discount_tag_id in tag_ids:
                return
            else:
                # 不在的话，需要判断已有的标签id中是否存在其他的打折标签，有的话需要替换成要打的这个标签
                for tag_id in tag_ids[::-1]:
                    if tag_id in discount_tag_ids:
                        tag_ids.remove(tag_id)
                        break

                # 剔除原有标签的列表，需要加上此次需要打的标签
                tags_ids.append(discount_tag_id)
                tags_ids.extend(tag_ids)

        # print(tags_ids)
        self.write_tag_goods(stock_related.get("merchant_id"), code, tags_ids)

        return

    def get_stock_related(self, hongde_merchant_ids):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
            #
            # hongde_merchant_ids.extend(self.hongde_group)

            hongde_group_str = ''
            for index, data in enumerate(hongde_merchant_ids):

                if index == len(hongde_merchant_ids) - 1:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + ""
                    break
                else:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + "'"

            hongde_group_str = hongde_group_str[3:]

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id not in ('{hongde_group_str}') """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_stock_related_single(self):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
            #
            # hongde_merchant_ids.extend(self.hongde_group)

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1  """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_cst_goods(self, cst_merchant_id):
        """
        根据厂商通id查找所有商品数据
        """

        # 测试环境
        # url = "http://192.168.191.131:8900/v1/api/openapi/goods/xqp/alllist"
        # 正式环境
        url = "http://192.168.191.245:9960/v1/api/openapi/goods/xqp/alllist"

        headers = {
            "Content-Type": "application/json"
        }

        params = {
            'yc_id': 'xqp0000000000000000001',
            'merchant_id': cst_merchant_id
        }
        response = requests.get(url=url, headers=headers, params=params)
        resp = response.json()

        if resp.get('code') != 1:
            print("厂商通id查找所有商品数据失败")

        return resp['data']

    def init_quality_replace(self, merchant_id):
        """
        初始化品质数据
        """
        mysql_conn = self.mysql_conn.connection()

        quality_data = {}

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select ori_quality, quality from diablo_quality_replace where merchant_id='{merchant_id}' and status=1"""

            cursor.execute(sql)

            result = cursor.fetchall()
            if len(result) > 0:

                for data in result:
                    ori_quality = data[0]
                    quality = data[1]

                    quality_data[ori_quality] = quality

            return quality_data, False

        except Exception as e:
            print("获取品质信息失败 - mysql：", e)
            return quality_data, True

        finally:
            mysql_conn.close()

    def get_discount_tag_list(self, merchant_id):
        """
        获取折扣标签字典
        """

        check = False
        discount_tag_data = {}

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["tag"]

                filter_dict = {
                    "merchant_id": merchant_id,
                    "tag_type": "goods",
                    "delete_time": {"$exists": False},
                    "type": 3
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    # 获取定价
                    docs = conn.find(filter_dict, {'tag_id': 1, 'name': 1})

                    for doc in docs:
                        tag_id = doc.get("tag_id", "")
                        name = doc.get("name", "")
                        discount_tag_data[name] = tag_id
                        check = True

                    return check, discount_tag_data

        except Exception as e:
            print("获取折扣标签字典失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check, discount_tag_data

    def bulk_operate_tag(self, bulk_insert_list, bulk_update_list):
        """批量操作 写入、更新等"""

        operate_list = []

        if bulk_insert_list:
            for data in bulk_insert_list:
                operate_list.append(InsertOne(data))

        if bulk_update_list:
            for data in bulk_update_list:
                operate_list.append(UpdateOne(data.get("filter_dict"), data.get("update_dict"), upsert=True))

        # 执行批量更新
        # 获取2.0MongoDB连接
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["ddll"]
                conn = db["tag"]
                conn.bulk_write(operate_list)

        except Exception as e:
            print("写入2.0MongoDB数据库单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def create_init_tag_data(self, stock_related):
        """
        初始创建折扣标签
        """

        merchant_id = stock_related.get('merchant_id')

        seed_tag = [{"type": 3, "tags": ["一折", "二折", "三折", "四折", "五折", "六折", "七折", "八折", "九折"]},
                    {"type": 1, "tags": ["热门商品", "特价商品"]}]

        bulk_insert_list = []
        bulk_update_list = []

        for seed in seed_tag:

            type = seed.get("type")

            tag_list = seed.get("tags")

            for tag in tag_list:

                filter_dict_exist = {
                    "merchant_id": merchant_id,
                    "tag_type": "goods",
                    "name": tag,
                    "type": type,
                    'is_system': 1
                }

                update_dict = {"$set": {
                    "merchant_id": merchant_id,
                    "tag_type": "goods",
                    "name": tag,
                    "type": type,
                    'tag_id': uuid.uuid4().hex,
                    'is_system': 1,
                    'username': "system",
                    'create_time': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                    'update_time': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                }}

                update_items = {"filter_dict": filter_dict_exist, "update_dict": update_dict}
                bulk_update_list.append(update_items)

        if len(bulk_update_list) > 0:
            try:
                self.bulk_operate_tag(bulk_insert_list, bulk_update_list)
            except Exception as e:
                print("批量标签记录失败：", e)

        return

    # 任务执行
    def task(self):
        q = Queue(maxsize=250)  # 设定最大队列数和线程数

        count = 1

        # hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
        # hongde_merchant_ids.extend(self.hongde_group)

        hongde_merchant_ids = []
        hongde_merchant_ids.extend(self.main_group_dev)
        hongde_merchant_ids.extend(self.hongde_group_dev)

        # 获取所有宏德库存记录
        # stock_related_list = self.get_stock_related(hongde_merchant_ids)

        stock_related_list = self.get_stock_related_single()

        for stock_related in stock_related_list:

            # 初始化当前公司基础标签，热门商品、折扣标签
            self.create_init_tag_data(stock_related)

            goods_cst_id = stock_related.get("goods_cst_id")

            # 获取库存商品列表
            goods_list = self.get_cst_goods(goods_cst_id)

            # 获取默认价格模板
            err, agreement_excel_id = self.get_merchant_agreement_excel(stock_related.get("merchant_id"))
            if err:
                print(f"商户{stock_related.get('merchant_id')}没有默认价格模板，循环跳出")
                continue

            # 获取当前公司一折到九折tag
            check, discount_tag_data = self.get_discount_tag_list(stock_related.get("merchant_id"))
            if not check:
                print(f"商户{stock_related.get('merchant_id')}没有默认折扣标签，循环跳出")
                continue

            # 初始化质保替换
            quality_dict, err = self.init_quality_replace(stock_related.get("merchant_id"))
            if err:
                print(f"商户{stock_related.get('merchant_id')}获取质保替换数据报错，循环跳出")
                continue

            for goods in goods_list:

                t = threading.Thread(target=self.write_data, args=(goods, stock_related, agreement_excel_id, quality_dict, discount_tag_data))
                q.put(t)
                if q.full() == True:
                    thread_list = []
                    while q.empty() == False:
                        t = q.get()
                        thread_list.append(t)
                        t.start()
                    for t in thread_list:
                        t.join()

                print("当前写入数：", count,
                      ", 商家为：", stock_related.get("merchant_name"),
                      ", 商品编码为：", goods.get("code"),
                      ", 名称为：", goods.get("part_name"))

                count += 1

            # 处理残余队列数据
            thread_list = []
            while q.empty() == False:
                t = q.get()
                thread_list.append(t)
                t.start()
            for t in thread_list:
                t.join()


if __name__ == "__main__":
    InitAgreementPriceTag()
